package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import model.CourseScore;
import model.Emp;
import model.Train;
import model.TrainCourse;
import model.TrainType;
import util.DBUtil;
import util.Page;
import dao.CourseScoreDAO;

public class CourseScoreDAOImpl implements CourseScoreDAO {

	public Page<CourseScore> getAllCourseScore(int pageNo, int pageSize) {
		Page<CourseScore> page = new Page<CourseScore>(pageNo, pageSize);
		int totalCount = 0;
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<CourseScore> list = new ArrayList<CourseScore>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from COURSESCORE";
			pstat=conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from COURSESCORE) s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo()*pageSize);
			pstat.setInt(2,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				CourseScore cs=new CourseScore();
				cs.setId(rs.getInt("ID"));
				cs.setEmp(new EmpDAOImpl().getEmpById(rs.getInt("EMPID")));
				cs.setTrainCourse(new TrainCourseDAOImpl().getById(rs.getInt("TRAINCOURSEID")));
				cs.setScore(rs.getDouble("SCORE"));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
		
	}

	public Page<CourseScore> getCourseScoreByEmpId(int pageNo, int pageSize,
			Emp emp) {
		// TODO Auto-generated method stub
		return null;
	}

	public Page<CourseScore> getCourseScoreBytrainCourse(int pageNo,
			int pageSize, TrainCourse trainCourse) {
		// TODO Auto-generated method stub
		return null;
	}

	public CourseScore getById(int id) {
		// TODO Auto-generated method stub
		return null;
	}

	public boolean addCourseScore(CourseScore courseScore) {
		
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		boolean flag=true;
		try{
			conn=dbu.getConnection();
			String sql="insert into  COURSESCORE(ID,EMPID,TRAINCOURSEID,SCORE) values(COURSESCORE_ID.nextval,?,?,?)";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, courseScore.getEmp().getId());
			pstat.setInt(2, courseScore.getTrainCourse().getId());
			pstat.setDouble(3, courseScore.getScore());
			pstat.executeUpdate();
		}catch(Exception e)
		{
			flag=false;
			e.printStackTrace();
			
		}finally
		{
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}

	public boolean updateCourseScore(CourseScore courseScore) {
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		boolean flag=true;
		try{
			conn=dbu.getConnection();
			String sql="update  COURSESCORE set SCORE=? where EMPID=? and TRAINCOURSEID=?";
			pstat = conn.prepareStatement(sql);
			pstat.setDouble(1, courseScore.getScore());
			pstat.setInt(2, courseScore.getEmp().getId());
			pstat.setDouble(3, courseScore.getTrainCourse().getId());
			pstat.executeUpdate();
		}catch(Exception e)
		{
			flag=false;
			e.printStackTrace();
			
		}finally
		{
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}

	public boolean deleteCourseScore(CourseScore CourseScore) {
		// TODO Auto-generated method stub
		return false;
	}

	public boolean deleteById(int id) {
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		boolean flag=true;
		try{
			conn=dbu.getConnection();
			String sql="delete from  COURSESCORE where EMPID=?";
			pstat = conn.prepareStatement(sql);			
			pstat.setInt(1, id);
			pstat.executeUpdate();
		}catch(Exception e)
		{
			flag=false;
			e.printStackTrace();
			
		}finally
		{
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}

	public boolean deleteByTrainCourse(TrainCourse trainCourse) {
		// TODO Auto-generated method stub
		return false;
	}

	public List<CourseScore> getCourseScoreByEmpId(int empid, int trainscoreid) {
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<CourseScore> list = new ArrayList<CourseScore>();
		try {
			conn = dbu.getConnection();
			String sql = "select * from COURSESCORE where EMPID=? and TRAINCOURSEID=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, empid);
			pstat.setInt(2,trainscoreid);
			rs = pstat.executeQuery();
			while (rs.next()) {
				CourseScore cs=new CourseScore();
				cs.setId(rs.getInt("ID"));
				cs.setEmp(new EmpDAOImpl().getEmpById(rs.getInt("EMPID")));
				cs.setTrainCourse(new TrainCourseDAOImpl().getById(rs.getInt("TRAINCOURSEID")));
				cs.setScore(rs.getDouble("SCORE"));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return list;
	}

}
